/*
 * Copyright 2007-208 the original author or authors.
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 * 
 *      http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */ 
package com.cassevern.storproc.util

import groovy.sql.*
import groovy.text.*


/* sample usage:
	new com.cassevern.storproc.util.BuildCallBeansFromStorProc(ip:'172.16.1.4', user:'DGD', password:'vo2max', library:'dgd', procName:'getaccts', handleResultSet:true).buildResultBean()
*/

class BuildCallBeansFromStorProc { 
	// these need to be passed:
	def ip = '172.16.1.4'
	def user = 'DGD'
	def password = 'vo2max'
	def library = 'dgd'
	def procName = 'getaccts'
	def handleResultSet = true
	
	void run () {
		if (handleResultSet) {
			buildResultBean()
		}
		buildTestCode()
		buildJavaCallBean()
		buildGroovyCallBean()
	}

	
	protected def getParmAttrList(def conn) {
		def list = []
		def rs = conn.getMetaData().getProcedureColumns(null, library.toUpperCase(), procName.toUpperCase(), null)
		while (rs.next()) {
			def column_name = rs.getString('column_name').toLowerCase()// column/parameter name
			def column_type = rs.getShort('column_type')// kind of column/parameter:
			    // 0: procedureColumnUnknown - nobody knows
			    // 1: procedureColumnIn - IN parameter
			    // 2: procedureColumnInOut - INOUT parameter
			    // 3: procedureColumnOut - OUT parameter
			    // 4: procedureColumnReturn - procedure return value -- but test show it's OUT
			    // 5: procedureColumnResult - result column in ResultSet 
		
			def data_type = rs.getInt('data_type')// SQL type from java.sql.Types
			def type_name = rs.getString('type_name')// SQL type name, for a UDT type the type name is fully qualified
			def precision = rs.getInt('precision')// precision
			def length = rs.getInt('length')// length in bytes of data
			def scale = rs.getShort('scale')// scale
			def nullable = rs.getShort('nullable')// can it contain NULL.
			    // 0: procedureNoNulls - does not allow NULL values
			    // 1: procedureNullable - allows NULL values
			    // 2: procedureNullableUnknown - nullability unknown 
		
			def remarks = rs.getString('remarks')// comment describing parameter/column 
			list << new ParmAttr(name:column_name, colType:column_type, type:type_name, 'length':length, 'precision':precision, 'scale':scale)
		}
		//println "// stored procedure parameters: "
		//list.each {println "name: $it.name colType: $it.colType type: $it.type length: $it.length precision: $it.precision scale: $it.scale"}
				
		return list
	}
	protected String getSQLCallStatement(def parms) {
		def calSQL = ''
		if (handleResultSet) {
			calSQL <<= 'def rows = sql.rows'
		} else {
			calSQL <<= 'sql.call'
		}
		calSQL <<= "(\"call ${library}.${procName}("
		parms.each {	calSQL <<= "?, " }
		calSQL = calSQL.toString().replaceAll(/, $/, '') //	remove trailling comma
		calSQL <<= ')\", ' // close parameter markers 
		
		calSQL <<= '[' // open input array
		parms.each {
			if (it.colType == 2 && !handleResultSet) {
				calSQL <<= "Sql.inout("
			}
			switch (it.type) {
			case 'DECIMAL':
			case 'NUMERIC':
				calSQL <<= "Sql.DECIMAL"; break;
			case 'CHARACTER': 	 
				calSQL <<= "Sql.CHAR"; break;
			default: println "ERROR: type ${type} not handled"
			}
			if (it.colType in 1..2) {
				calSQL <<= "(${it.name})"
			}
			if (it.colType == 2 && !handleResultSet) {
				calSQL <<= ") "
			}
			calSQL <<= ", "
		}
		calSQL = calSQL.toString().replaceAll(/, $/, '') // remove trailling comma 
		calSQL <<= '])' // close input array
		return calSQL
	}

	protected def getConnection() {
		return Sql.newInstance("jdbc:as400://$ip/library", user, password, "com.ibm.as400.access.AS400JDBCDriver").getConnection()
	}

	private boolean isProcedureAvail(def conn) {
		def rs = conn.getMetaData().getProcedures(null, library.toUpperCase(), procName.toUpperCase())
		if (!rs.next()) {
			throw new Exception("No procedure with the name $library/$procName is available")
		}
		if (rs.next()) {
			println "WARN: more than one procedure with the name $library/$procName, using the first"
		}
		return true
	}
	
	String buildResultBean() {
		def conn = getConnection()
		isProcedureAvail(conn) 
		def list = getParmAttrList(conn)
		def inputParms = list.findAll { it.colType in 1..2 }
		def outputParms =   list.findAll { it.colType in 2..3 }
		
		def sqlCallStatement = getSQLCallStatement(inputParms)
		def procNameCamelCase = procName.substring(0,1).toUpperCase() + procName.substring(1).toLowerCase()

		def file = new File("src/com/cassevern/storproc/util/RpgCallJavaBeanResultGUI.template")
		def binding = [inputParms:inputParms, outputParms:outputParms,
		               ip:ip,user:user,password:password,library:library,
		               procNameCamelCase:procNameCamelCase,sqlCallStatement:sqlCallStatement,
		               handleResultSet:handleResultSet
		               ]
		def writable = new SimpleTemplateEngine().createTemplate(file).make(binding)
		GroovyShell shell = new GroovyShell()
		shell.evaluate(writable.toString())
		return writable.toString()
	}

	private def getCallAPIParms(def inputParms) {
		def callParms = ''
		inputParms.each {
			switch (it.type) {
			case 'DECIMAL':
			case 'NUMERIC':
				callParms <<= "BigDecimal "; break;
			case 'CHARACTER': 	 
				callParms <<= "String "; break;
			default: println "ERROR: type ${type} not handled"
			}
			callParms <<= "$it.name, "
		}
		callParms = callParms.toString().replaceAll(/, $/, '') // remove trailling comma 
		return callParms
	}
	
	String buildJavaCallBean() {
		def conn = getConnection()
		isProcedureAvail(conn)
		def procNameCamelCase = procName.substring(0,1).toUpperCase() + procName.substring(1).toLowerCase()
		def list = getParmAttrList(conn)
		def inputParms = list.findAll { it.colType in 1..2 }
		def outputParms =   list.findAll { it.colType in 2..3 }

		def questionMarkPlaceholders = ''
		inputParms.each {questionMarkPlaceholders <<= "?, "}
		questionMarkPlaceholders = questionMarkPlaceholders.toString().replaceAll(/, $/, '') //	remove trailling comma

		def regOutParms = ''
		outputParms.eachWithIndex { parm, i ->
			regOutParms <<= "\t\tcallRpg.registerOutParameter(${i+1}, Types."
			switch (parm.type) {
			case 'DECIMAL'  : case 'NUMERIC': regOutParms <<= "DECIMAL );\n"; break;
			case 'CHARACTER': 	              regOutParms <<= "CHAR );\n";    break;
			default: println "ERROR: buildJavaCallBean type ${type} not handled"
			}
		}
		def regInParms = ''
		inputParms.eachWithIndex { parm, i ->
			regInParms <<= "\t\tcallRpg.set"
			switch (parm.type) {
			case 'DECIMAL'  : case 'NUMERIC': regInParms <<= "BigDecimal(${i+1}, "; break;
			case 'CHARACTER': 	              regInParms <<= "String(${i+1}, ";    break;
			default: println "ERROR: buildJavaCallBean type ${type} not handled"
			}
			regInParms <<= parm.name + ');\n'
		}

		def callAPIParms = getCallAPIParms(inputParms);
		def file = new File("src/com/cassevern/storproc/util/RpgCallJavaBean.template")
		def binding = [procNameCamelCase:procNameCamelCase,library:library,procName:procName,
		               inputParms:inputParms,regOutParms:regOutParms,regInParms:regInParms,callAPIParms:callAPIParms,
		               questionMarkPlaceholders:questionMarkPlaceholders]
		def writable = new SimpleTemplateEngine().createTemplate(file).make(binding)
			new File("src/com/cassevern/rpgbeans/${procNameCamelCase}.java").write(writable.toString())
		println writable.toString()
	}

	String buildGroovyCallBean() {
		def conn = getConnection()
		isProcedureAvail(conn)
		def procNameCamelCase = procName.substring(0,1).toUpperCase() + procName.substring(1).toLowerCase()
		def parms = getParmAttrList(conn)
		def inputParms = parms.findAll { it.colType in 1..2 }
		def outputParms = parms.findAll { it.colType in 2..4 }

		def list = [:]
		def sqlCallStatement = getSQLCallStatement(parms)
		def callAPIParms = getCallAPIParms(inputParms);
		
		def file = new File("src/com/cassevern/storproc/util/RpgCallGroovyBean.template")
		def binding = [procNameCamelCase:procNameCamelCase,callAPIParms:callAPIParms,
		               handleResultSet:handleResultSet,
		               outputParms:outputParms,inputParms:inputParms,
		               sqlCallStatement:sqlCallStatement]
		def writable = new SimpleTemplateEngine().createTemplate(file).make(binding)
		new File("src/com/cassevern/rpgbeans/${procNameCamelCase}.groovy").write(writable.toString())
		println writable.toString()
		return writable.toString()
	}
	
	String buildTestCode() {
		def procNameCamelCase = procName.substring(0,1).toUpperCase() + procName.substring(1).toLowerCase()
		def conn = getConnection()
		def list = getParmAttrList(conn)
		def inputParms = list.findAll { it.colType in 1..2 }
		def argSettings = ''
		inputParms.each {
			switch (it.type) {
			case 'DECIMAL':
			case 'NUMERIC':
				argSettings <<= "new BigDecimal(0), "; break;
			case 'CHARACTER': 	 
				argSettings <<= "\'\', "; break;
			default: println "ERROR: type ${type} not handled"
			}
		}
		argSettings = argSettings.toString().replaceAll(/, $/, '') // remove trailling comma 

		def file = new File("src/com/cassevern/storproc/util/RpgCallBeanTest.template")
		def binding = [ip:ip,user:user,library:library,argSettings:argSettings,procNameCamelCase:procNameCamelCase,handleResultSet:handleResultSet]
		def writable = new SimpleTemplateEngine().createTemplate(file).make(binding)
		new File("src/com/cassevern/rpgbeans/${procNameCamelCase}Test.groovy").write(writable.toString())
		return writable.toString()
	}
}
		